import pandas as pd
import pymysql, os, nltk, re, jieba
from collections import Counter


# 切换工作目录到脚本所在的路径
os.chdir(os.path.dirname(os.path.abspath(__file__)))

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='your_password',
    db='your_db_name',
    charset='utf8mb4'
)

cursor = connection.cursor()

# 下载停用词列表（如果没有下载过）
nltk.download('stopwords')
from nltk.corpus import stopwords
# 获取停用词
stop_words = set(stopwords.words('chinese'))  # 如果是英文可以替换为 'english'

# 处理简介文本，分词并去除停用词
def process_text(text):
    # 去除标点符号和多余空格
    text = re.sub(r'[^\w\s]', '', text)  # 去除标点符号
    text = re.sub(r'\s+', ' ', text)  # 去除多余空格
    text = text.strip()

    # 使用 jieba 分词
    words = jieba.cut(text)

    # 去除停用词并返回分词列表
    return [word for word in words if word not in stop_words and len(word) > 1]


# 清空指定表的数据
def clear_table(table_name):
    try:
        # 删除表数据
        sql = f"DELETE FROM {table_name};"
        cursor.execute(sql)
        connection.commit()

        # 重置自增值
        sql = f"ALTER TABLE {table_name} AUTO_INCREMENT = 1;"
        cursor.execute(sql)
        connection.commit()

    except Exception as e:
        print(f"清空表 {table_name} 失败: {e}")
        connection.rollback()

def run_sql():
    df = pd.read_csv('food1.csv', encoding='utf-8')

     # 根据类型合并所有简介内容
    grouped = df.groupby('类型')['简介'].apply(' '.join).reset_index()

    # 处理数据并插入数据库
    for idx, row in grouped.iterrows():
        foodtype = row['类型']  # 获取菜品类型
        description = row['简介']  # 获取合并后的简介内容

        # 对合并后的简介进行分词处理
        words = process_text(description)
        
        # 统计词频
        word_count = Counter(words)

        # 插入每个词及其频率
        for word, count in word_count.items():
            # 插入大于5频率的词
            if count > 5:
                data = (count, word, foodtype)
                sql = "INSERT INTO wordanalysis(value, name, foodtype) VALUES (%s, %s, %s);"
                try:
                    cursor.execute(sql, data)
                    connection.commit()
                except Exception as e:
                    print(f"插入数据失败: {e}")
                    connection.rollback()

    # 美食类型
    cate_num = list(df['类型'].value_counts())
    cate_list = list(df['类型'].value_counts().index)

    # 评论 
    comment_num = list(df.sort_values(by='评论数量', ascending=False)['评论数量'])[:20]
    comment_list = list(df.sort_values(by='评论数量', ascending=False)['标题'])[:20]

    # 收藏
    collect_num = list(df.sort_values(by='收藏数量', ascending=False)['收藏数量'])[:20]
    collect_list = list(df.sort_values(by='收藏数量', ascending=False)['标题'])[:20]

    for i in range(len(cate_num)):
        data1 = (cate_num[i], cate_list[i])
        sql = "insert into cateanalysis(cate_num,cate_list) values" + str(data1) + ';'
        try:
            cursor.execute(sql)
            connection.commit()
        except:
            connection.rollback()

    for i in range(len(comment_num)):
        data1 = (comment_num[i], comment_list[i])
        sql = "insert into commentanalysis(comment_num,comment_list) values" + str(data1) + ';'
        try:
            cursor.execute(sql)
            connection.commit()
        except:
            connection.rollback()
    
    for i in range(len(collect_num)):
        data1 = (collect_num[i], collect_list[i])
        sql = "insert into collectanalysis(collect_num,collect_list) values" + str(data1) + ';'
        try:
            cursor.execute(sql)
            connection.commit()
        except:
            connection.rollback()
    cursor.close()
    connection.close()

try:
    clear_table("cateanalysis")
    clear_table("commentanalysis")
    clear_table("collectanalysis")
    clear_table("wordanalysis")
    run_sql()
    print("分析数据成功插入数据库！")
except Exception as e:
    print("出现错误！"+str(e))